<%@ page import="jxl.Workbook" %>
<%@ page import="jxl.Sheet" %>
<%@ page import="jxl.Cell" %>
<%@ page pageEncoding="UTF-8"%>
<%@ include file="/common/common.jsp"%>
<%!
    public List getExpertList(Map deptMap ,String url,Integer hospital){
        //然后读取excl
        Workbook book = null;
        try {
            System.out.println(url);
            book = Workbook.getWorkbook(new File(url));

            Sheet sheet = book.getSheet(0);
            int rows=sheet.getRows();
            int columns=sheet.getColumns();

            List expertList = new ArrayList();//存储集合

            for(int i = 0 ; i < rows ; i++ ){
                String deptName = sheet.getCell(0, i).getContents();
                String expertName = sheet.getCell(1, i).getContents();
                String avatar = sheet.getCell(2, i).getContents();
                String title = sheet.getCell(3, i).getContents();
                String content = sheet.getCell(4, i).getContents();

                String time = "";
                try{
                    time = sheet.getCell(5, i) == null  ? "" : sheet.getCell(5, i).getContents();
                }catch (Exception e){

                }
                if("".equals(deptName)){
                    continue;
                }
                Expert expert = new Expert();
                expert.setExpertId(Tools.getMD5(expertName));
                expert.setName(expertName);
                expert.setAvatar(avatar);
                expert.setTitle(title);
                expert.setTime(time);
                expert.setContent(content);
                expert.setHospitalOrder(i+1);
                expert.setHospital(hospital);
                expert.setStatus(1);
                expert.setcTime(System.currentTimeMillis());
                expert.setDeptId(deptMap.get(deptName) == null ? -1 : Integer.parseInt(deptMap.get(deptName).toString()));
                expert.setJsonString("{}");
                expertList.add(expert);
            }
            return  expertList;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
%>
<%
    //创建部门 DEPT_REGISTER =9
    hospital = 1113;
    //先删除部门
    QBuilder.db("basedb").execHQL("delete from Expert where hospital = " + hospital );
    List<Map> deptList = QBuilder.db("basedb").queryHql("select new map(id as id ,deptName as deptName) from Dept where hospital = " + hospital);
    Map deptMap = new HashMap();
    if(deptList != null && deptList.size() >0){
        for(Map map :deptList){
            String id  = map.get("id").toString();
            String deptName  = map.get("deptName").toString();
            deptMap.put(deptName,id);
        }
    }

    List expertList = getExpertList(deptMap,(request.getServletContext().getRealPath("/") + "patient/1113/expert/expert1.xls"),hospital);
    List<Expert> expertList2 = getExpertList(deptMap,(request.getServletContext().getRealPath("/") + "patient/1113/expert/expert2.xls"),hospital);
    //对中山专家做特殊处理
    for(Expert expert :expertList2){
        expert.setJsonString("{\"branchName\":\"中山专家\"}");
    }
    expertList.addAll(expertList2);
    DBTools.db("basedb").addItem(expertList);
    out.println("执行完毕");

%>
